﻿using System;
using System.ComponentModel.DataAnnotations;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using Microsoft.VisualBasic.FileIO;

namespace DAL
{
    public class InventizerDatabaseInstance
    {
        private SqlDatabase dbInstance;

        public string connectionString
        {
            get
            {
                return ConfigurationManager
                    .ConnectionStrings["InventizerConnectionString"]
                    .ConnectionString;
            }
        }
        public InventizerDatabaseInstance()
        {
            dbInstance = new SqlDatabase(connectionString);
        }

        protected const string _truncateLiveTableCommandText = @"TRUNCATE TABLE MBEW";
        protected const int _batchSize = 100000;


        public void LoadCsvDataIntoSqlServer()
        {
            // This should be the full path
            var fileName = @"C:\tfs\Inventizer\InventizerWEB\LoadedFiles\MBEWTAB.txt";

            var createdCount = 0;

            using (var textFieldParser = new TextFieldParser(fileName))
            {
                textFieldParser.TextFieldType = FieldType.Delimited;
                textFieldParser.Delimiters = new[] { "\t" };
                textFieldParser.HasFieldsEnclosedInQuotes = true;

                var dataTable = new DataTable("MBEW");

                // Add the columns in the temp table

                dataTable.Columns.Add("PARTNUMBER");
                dataTable.Columns.Add("PRICE");
                dataTable.Columns.Add("UNITPRICE");
                dataTable.Columns.Add("CLASSVALUATION");

                using (var sqlConnection = new SqlConnection(connectionString))
                {
                    sqlConnection.Open();

                    // Truncate the live table
                    using (var sqlCommand = new SqlCommand(_truncateLiveTableCommandText, sqlConnection))
                    {
                        sqlCommand.ExecuteNonQuery();
                    }

                    // Create the bulk copy object
                    var sqlBulkCopy = new SqlBulkCopy(sqlConnection)
                    {
                        DestinationTableName = "MBEW"
                    };

                    // Setup the column mappings, anything ommitted is skipped
                    sqlBulkCopy.ColumnMappings.Add("INVENTORYID", "INVENTORYID");
                    sqlBulkCopy.ColumnMappings.Add("PARTNUMBER", "PARTNUMBER");
                    sqlBulkCopy.ColumnMappings.Add("PRICE", "PRICE");
                    sqlBulkCopy.ColumnMappings.Add("UNITPRICE", "UNITPRICE");
                    sqlBulkCopy.ColumnMappings.Add("CLASSVALUATION", "CLASSVALUATION");
                    var dc = new DataColumn
                    {
                        DefaultValue = 1,
                        ColumnName = "INVENTORYID",
                        DataType = typeof(int)
                    };
                    dataTable.Columns.Add(dc);
                    while (!textFieldParser.EndOfData)
                    {
                        dataTable.Rows.Add(textFieldParser.ReadFields());
                        createdCount++;

                        if (createdCount % _batchSize == 0)
                        {
                            InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);
                            break;
                        }
                    }

                    // Don't forget to send the last batch under 100,000
                    InsertDataTable(sqlBulkCopy, sqlConnection, dataTable);

                    sqlConnection.Close();
                }
            }
        }

        protected void InsertDataTable(SqlBulkCopy sqlBulkCopy, SqlConnection sqlConnection, DataTable dataTable)
        {
            sqlBulkCopy.WriteToServer(dataTable);

            dataTable.Rows.Clear();
        }

        public void LoadBulkToMBEW(DataTable dt)
        {
            try
            {
                using (var conn = new SqlConnection(connectionString))
                {
                    conn.Open();
                    using (var transaction = conn.BeginTransaction())
                    {
                        using (var bulkCopy = new SqlBulkCopy(
                            conn, SqlBulkCopyOptions.KeepIdentity,
                            transaction))
                        {
                            bulkCopy.BatchSize = 500;
                            bulkCopy.DestinationTableName = "dbo.MBEW";
                            try
                            {
                                bulkCopy.WriteToServer(dt);
                                transaction.Commit();
                            }
                            catch (Exception ex)
                            {
                                transaction.Rollback();
                                throw new Exception(ex.Message);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
